﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
namespace TongJi
{
    class ExecuteExcel
    {

        public void exportExcel(System.Data.DataTable dt,string excelName,string gplname,string gamename)
        {
            //首先模拟建立将要导出的数据，这些数据都存于DataTable中


            //创建Excel

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Workbook excelWB = excelApp.Workbooks.Add(System.Type.Missing);    //创建工作簿（WorkBook：即Excel文件主体本身）
            Worksheet excelWS = (Worksheet)excelWB.Worksheets[1];   //创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出

            excelWS.Cells.NumberFormat = "@";     //  如果数据中存在数字类型 可以让它变文本格式显示

            Range range = null;
            excelWS.Name = gamename; //设置工作表名  
            range = (Range)excelWS.get_Range("A1", "K1"); //获取Excel多个单元格区域：本例做为Excel表头   
            range.Merge(0); //单元格合并动作   要配合上面的get_Range()进行设计  
            excelWS.Cells[1, 1] = gplname; //Excel单元格赋值
            range.Font.Size = 15; //设置字体大小
            range.Font.Name = "黑体"; //设置字体的种类 
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式 
            range.EntireColumn.AutoFit(); //自动调整列宽 
            range.Interior.ColorIndex = 39; //填充颜色为淡紫色 
            excelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存 

            excelWS.Cells[2, 1] = "账号";
            excelWS.Cells[2, 2] = "角色名";
            excelWS.Cells[2, 3] = "等级";
            excelWS.Cells[2, 4] = "性别";
            excelWS.Cells[2, 5] = "职业";
            excelWS.Cells[2, 6] = "元宝";
            excelWS.Cells[2, 7] = "绑定元宝";
            excelWS.Cells[2, 8] = "铜钱";
            excelWS.Cells[2, 9] = "绑定铜钱";
            excelWS.Cells[2, 10] = "是否在线";
            excelWS.Cells[2, 11] = "注册时间";

            
            //将数据导入到工作表的单元格
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    
                    excelWS.Cells[i + 3, j + 1] = dt.Rows[i][j].ToString();   //Excel单元格第一个从索引1开始
                }
            }

            excelWB.SaveAs(excelName);  //将其进行保存到指定的路径
            excelWB.Close();
            excelApp.Quit();  //KillAllExcel(excelApp); 释放可能还没释放的进程
            KillAllExcel(excelApp);
        }
        #region 释放Excel进程
        public bool KillAllExcel(Microsoft.Office.Interop.Excel.Application excelApp)
        {
            try
            {
                if (excelApp != null)
                {
                    excelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    //释放COM组件，其实就是将其引用计数减1   
                    //System.Diagnostics.Process theProc;   
                    foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
                    {
                        //先关闭图形窗口。如果关闭失败.有的时候在状态里看不到图形窗口的excel了，   
                        //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要释放它   
                        if (theProc.CloseMainWindow() == false)
                        {
                            theProc.Kill();
                        }
                    }
                    excelApp = null;
                    return true;
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        #endregion
    }
}
